Bottom-up Coffee Shop Financial Model (Excel XLSX)

This is a financial planning template for a single location coffee shop. The menu builder is detailed enough to be used for a restaurant or Café as well. It is important to understand the unit economics of your shop and with the bottom-up assumptions in this template, you can do just that.

For each menu item, the assumptions allow for an input of price and expected monthly sales volume over time. To accurately define the cost of goods sold, you can go through each item and enter its average cost. This makes it easy to see how profitable your menu is at various prices and volumes.

There is also a simple input for operating expenses. After all the assumptions have been defined, you will get automatically populated reports that include monthly and annual financial statements, a monthly and annual pro forma detail, an annual executive summary, and visualizations that make it easier to understand the financial forecast being created.

I have designed the model to solve for the minimum investment (equity required) to keep the cash balance from ever going below 0. This considers initial acquisition / development costs and any net burn from your initial operations.

Some of the metrics you will see include EBITDA, NPV, IRR, (option for outside / inside investors), DSCR (if you are financing any of the startup costs), and Net Income. There is also a full DCF Analysis for the project level and inside / outside investor level. Any assumptions that are not needed can be zeroed out.

To get a better sense of cash flows, there is an option to purchase the ingredients in bulk or simply have those costs happen month-to-month in conjunction with the sales volume. This will depend on the type of ingredients and how long you can store them for.

Note, I did include a sensitivity table on the 'Global Control' tab that shows the resulting IRR of the project when prices and sales volumes are adjusted up or down.

The model comes with an instructional video and all the assumptions are blank so you can go in and follow along with the video to fill out all relevant inputs.